/*
 * @(#)FormDynamicValueDAObject.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.form.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.dcivision.form.bean.FormDBlookupSQL;
import com.dcivision.framework.Utility;

/**
 * FormDynamicValueDAObject.java
 * 
 * This class is the data access bean for DB lookup function.
 * 
 * @author Charlie liu
 * @company DCIVision Limited
 * @creation date 12/09/2006
 * @version $Revision: 1.5.2.2 $
 */

public class FormDynamicValueDAObject {

	public static final String REVISION = "$Revision: 1.5.2.2 $";

	protected Log log = LogFactory.getLog(this.getClass().getName());

	private Connection con;

	public FormDynamicValueDAObject(Connection dbConn) {
		super();
		this.con = dbConn;
	}
	public List getList(FormDBlookupSQL sql) {
		PreparedStatement sqlStatement = null;
		String sqlStr = null;
		ResultSet rs = null;
		List result = new ArrayList();
		try {			
			if (Utility.isEmpty(sql.getCondition().trim())) {
				sqlStr = "select " + sql.getField() + " from " + sql.getTable();
			} else {
				sqlStr = "select " + sql.getField() + " from " + sql.getTable()
						+ " where " + sql.getCondition();
			}
			log.debug("SQL statement: " + sqlStr);
			sqlStatement = con.prepareStatement(sqlStr);
			rs = sqlStatement.executeQuery();
			String value="" , label="" ;	 
			while (rs.next()) {
				label = rs.getString(1);
				if((sql.getField().split(",")).length>1){
					value = rs.getString(2);
				}else{
					value = "";
				}				
				log.debug("label=: " + label);
				log.debug("value=: " + value);
				if((sql.getField().split(",")).length>1){
					value= label+","+value;
				}else{
					value= label;
				}				
				result.add(value);
			}
			if (result == null || result.isEmpty()) {
				log.debug("Record not found");
			}
		} catch (Exception e) {
      log.error(e, e);
			return null;
		} finally {
			try {
				if (rs != null) {
          rs.close();
//				if (con != null)
//					con.close();
        }
			} catch (SQLException e) {
        log.error(e, e);
				throw new RuntimeException("error.unexpecte");
			}
		}
		return result;
	}

	public Object[] getDynamicValue(String sql,boolean more) {
		PreparedStatement sqlStatement = null;
		String sqlStr = null;
		ResultSet rs = null;
		String result = null;
		ArrayList results = new ArrayList();
		if (!Utility.isEmpty(sql)) {
			try {				
				sqlStr = sql;
				//log.debug("SQL statement: " + sqlStr);
				sqlStatement = con.prepareStatement(sqlStr);
				rs = sqlStatement.executeQuery();
				int i=0;
				while (rs.next()) {
					result = rs.getString(1);					
					results.add(i,result);
					if (!Utility.isEmpty(result)) {
						log.debug("value=: " + result);
						if(!more) {
              break;
            }
					} else {
						log.debug("Record not found");
					}
					i++;
				}
			} catch (Exception e) {
        log.error(e, e);
			} finally {
				try {
					if (rs != null) {
            rs.close();
//					if (con != null)
//						con.close();
          }
				} catch (SQLException e) {
          log.error(e, e);
					throw new RuntimeException("error.unexpecte");
				}
			}
		}
		log.debug("Record size is "+results.size());
		return results.toArray();
	}
}
